﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.IO;

using Microsoft.AnalysisServices.AdomdClient;

using Ko2.DB.BI;

namespace Ko2.DB.BI.Catalogs
{
    public class Shops : MXBase
    {

        public Exception GetAll(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            return GetAll(ref tbl, ConnStr, Year, Month);
        }

        public Exception GetAll(ref DataTable tbl, int parYear, int parMonth)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return GetAll(ref tbl, ConnStr, parYear, parMonth);
        }

        public Exception GetAll(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
        {
            #region Members

            Exception ret = null;
            string MonthStr = MakeDT_Y_M(parYear, parMonth);

            #region SQL
            /*
SELECT { AddCalculatedMembers({
[Магазины].[Магазин Список].[Магазин Список].Members} )}
DIMENSION PROPERTIES MEMBER_TYPE 
ON COLUMNS 
FROM [KPI] CELL PROPERTIES CELL_ORDINAL
            */
            string cmdStr =
    @"
SELECT { AddCalculatedMembers({
[Магазины].[Магазин Список].[Магазин Список].Members} )}
DIMENSION PROPERTIES MEMBER_TYPE 
ON COLUMNS 
FROM [KPI] CELL PROPERTIES CELL_ORDINAL
";

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_001(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
            }

            return ret;
        }

        public Exception Get(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            int Day = dt.Day;
            return Get(ref tbl, ConnStr, Year, Month, Day);
        }

        public Exception Get(ref DataTable tbl, int parYear, int parMonth, int Day)
        {
            string ConnStr = _env.GetAppSetting("BIConnect");
            return Get(ref tbl, ConnStr, parYear, parMonth, Day);
        }

        public Exception Get(ref DataTable tbl, string parConnStr, int parYear, int parMonth, int Day)
        {

            #region Members
            Exception ret = null;
            DateTime dt = new DateTime(parYear, parMonth, Day);
            if (tbl == null)
                tbl = new DataTable("tbl");
            tbl.Columns.Add("Id", typeof(string));
            tbl.Columns.Add("Name", typeof(string));
            #endregion Members

            for (int i = 0; i < 10; i++)
            {

                DataTable tblTmp = new DataTable("tblTmp");

                #region SQL
                /*
SELECT NON EMPTY 
Hierarchize(DrilldownMember({{
	DrilldownLevel({[Магазины].[Все].[Все]},,,INCLUDE_CALC_MEMBERS)}}
	, {[Магазины].[Все].[Активные магазины].&[Активные магазины]},,,INCLUDE_CALC_MEMBERS)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Магазины].[Все].[Магазин Список].[Активные магазины] 
ON COLUMNS  
FROM (SELECT ({[Магазины].[Все].[Активные магазины].&[Активные магазины]}) 
ON COLUMNS  
FROM [KPI]) 
WHERE ([Дата].[Г-М-Д].[Дата].[12.11.2011],[Measures].[Оборот Все Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
            */
                string cmdStr =
        @"
SELECT NON EMPTY 
Hierarchize(DrilldownMember({{
	DrilldownLevel({[Магазины].[Все].[Все]},,,INCLUDE_CALC_MEMBERS)}}
	, {[Магазины].[Все].[Активные магазины].&[Активные магазины]},,,INCLUDE_CALC_MEMBERS)) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Магазины].[Все].[Магазин Список].[Активные магазины] 
ON COLUMNS  
FROM (SELECT ({[Магазины].[Все].[Активные магазины].&[Активные магазины]}) 
ON COLUMNS  
FROM [KPI]) 
WHERE ([Дата].[Г-М-Д].[Дата].[" + MakeDT_D_M_Y(dt.Day, dt.Month, dt.Year) + @"],[Measures].[Оборот Все Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
";
                #endregion SQL

                CellSet cst = null;
                Exception err = Exec(ref cst, parConnStr, cmdStr);

                if (err != null)
                {
                    // Exec Error
                    ret = new Exception(err.Message);
                    break;
                }
                else
                {
                    err = Parse_002(ref cst, ref tblTmp);
                    if (err != null)
                    {
                        // No Rows
                        if (err.Message == @"No data returned for the selection")
                        {
                            dt = dt.AddDays(-1);
                            tblTmp = null;
                            continue;
                        }
                        // Parse Error
                        ret = new Exception(err.Message, err);
                    }
                    else
                    {
                        // No Rows
                        if (tblTmp.Rows.Count <= 0)
                        {
                            dt = dt.AddDays(-1);
                            tbl = null;
                            continue;
                        }
                        else
                        {
                            AppendTbl(ref tbl, ref tblTmp);
                            dt = dt.AddDays(-1);
                            continue;
                        }
                    }
                }
            }

            if ( tbl != null && tbl.Rows.Count > 0 )
            {
                DataView v = tbl.DefaultView;
                v.Sort = "Name";
            }

            return ret;
        }

        public Exception GetAllFromWS(ref DataTable tbl)
        {
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            return GetFromWS(ref tbl, Year, Month);
        }

        public Exception GetAllFromWS(ref DataTable tbl, int parYear, int parMonth)
        {

            #region Members

            Exception ret = null;
            //string MonthStr = MakeDT_Y_M(parYear, parMonth);
            List<String> errList = new List<string>();

            Ko2.Web.Utils.WS_Helper ws = new Web.Utils.WS_Helper();
            ret = ws.GetBI_ShopsAll(ref tbl, ref errList);

            #endregion Members

            return ret;
        }

        public Exception GetFromWS(ref DataTable tbl)
        {
            DateTime dt = DateTime.Now;
            int Year = dt.Year;
            int Month = dt.Month;
            return GetFromWS(ref tbl, Year, Month);
        }

        public Exception GetFromWS(ref DataTable tbl, int parYear, int parMonth)
        {
            #region Members

            Exception ret = null;
            //string MonthStr = MakeDT_Y_M(parYear, parMonth);
            List<String> errList = new List<string>();

            Ko2.Web.Utils.WS_Helper ws = new Web.Utils.WS_Helper();
            ret = ws.GetBI_Shops(ref tbl, ref errList);

            #endregion Members

            return ret;
        }

        public Exception Parse_001(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            try
            {

                #region Init

                // check if any axes were returned else throw error.
                int axes_count = parCst.Axes.Count;
                if (axes_count == 0)
                    return new Exception("No data returned for the selection");

                // if axes count is not 2
                if (axes_count != 1)
                    return new Exception("The sample code support only queries with two axes");

                // if no position on either row or column throw error
                if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                    return new Exception("No data returned for the selection");

                #endregion

                #region Members

                int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
                cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

                // Number of dimensions on the column
                col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

                // Number of dimensions on the row
                //if (parCst.Axes[1].Positions[0].Members.Count > 0)
                //    row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

                // Total rows and columns
                //row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
                col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

                MakeTbl_001(ref parCst, ref parTbl, col_count);

                int count = 0;
                DataRow r = null;

                #endregion

                #region for

                col_count = parCst.Axes[0].Positions.Count + row_dim_count;

                for (int i = 1; i < col_count - 1; i++)
                {
                    string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                    r = parTbl.NewRow();
                    r["Name"] = ColNameStr;
                    parTbl.Rows.Add(r);
                }

                #endregion

            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }

            return ret;
        }

        public Exception Parse_002(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            try
            {

                #region Init

                // check if any axes were returned else throw error.
                int axes_count = parCst.Axes.Count;
                if (axes_count == 0)
                    return new Exception("No data returned for the selection");

                // if axes count is not 1
                if (axes_count != 1)
                    return new Exception("The sample code support only queries with two axes");

                // if no position on either row or column throw error
                if (!(parCst.Axes[0].Positions.Count > 0))
                    return new Exception("No data returned for the selection");

                #endregion

                #region Members

                int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
                cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

                // Number of dimensions on the column
                col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

                // Number of dimensions on the row
                if (parCst.Axes[0].Positions[0].Members.Count > 0)
                    row_dim_count = parCst.Axes[0].Positions[0].Members.Count;

                // Total rows and columns
                //row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
                col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

                MakeTbl_001(ref parCst, ref parTbl, col_count);

                DataView v = parTbl.DefaultView;
                v.Sort = "Name";

                DataRow r = null;

                #endregion

                #region for

                col_count = parCst.Axes[0].Positions.Count + row_dim_count;

                for (int i = 1; i < col_count - 1; i++)
                {
                    string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                    r = parTbl.NewRow();
                    r["Name"] = ColNameStr;
                    parTbl.Rows.Add(r);
                }

                #endregion

            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }

            return ret;
        }

        public void MakeTbl_001(ref CellSet parCst, ref DataTable parTbl, int cnt)
        {
            //int row_dim_count = 0, col_count;
            if (parTbl == null)
                parTbl = new DataTable("tbl");
            parTbl.Columns.Add("Id", typeof(string));
            parTbl.Columns.Add("Name", typeof(string));
        }

        public void AppendTbl(ref DataTable tblDest, ref DataTable tblSrc)
        {
            string name = "";
            foreach (DataRow r in tblSrc.Rows)
            {
                name = "";
                if (r["Name"] != DBNull.Value)
                {
                    name = (string)r["Name"];
                    if (!IsExist(ref tblDest, name))
                    {
                        DataRow newr = tblDest.NewRow();
                        newr["Id"] = r["Id"];
                        newr["Name"] = name;
                        tblDest.Rows.Add(newr);
                    }
                }
            }
        }

        public bool IsExist(ref DataTable tbl, string name)
        {
            bool ret = false;
            foreach (DataRow r in tbl.Rows)
            {
                if (r["Name"] != DBNull.Value)
                {
                    if ((string)r["Name"] == name)
                    {
                        ret = true;
                        break;
                    }
                }
            }
            return ret;
        }

    }
}
